package com.huangpf.util.excelUtil;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.dom4j.Element;

import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by huangpf on 2016/9/1.
 */
public class ExcelGen {
    private Workbook wb;

    private ExcelXml excelXml;

    private Sheet sheet;

    private List list;

    private List<String> headerValues;

    private List<CellStyle> headerStyles;

    private List<List<String>> bodyValues;

    private List<CellStyle> bodyStyles;

    public ExcelGen(Workbook wb, ExcelXml excelXml, List list){
        this.wb = wb;
        this.excelXml = excelXml;
        this.list = list;

        // 初始化
        init();

        // 生成Workbook
        genWorkbook();
    }

    public void init(){
        if (excelXml.isHasHeader()){
            // 设置headerStyle
            this.initHeaderStyles();

            // 设置headerValus
            this.initHeaderValues();
        }

        // 设置bodyStyle
        initBodyStyles();
        // 设置bodyValues
        initBodyValues();
    }

    /**
     * 初始化样式，并且设置基础样式
     * @return
     */
    public CellStyle createDefaultCellStyle() {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);

        return cellStyle;
    }

    /**
     * 创建headerStyle，设置默认值
     */
    public CellStyle createHeaderStyle() {
        CellStyle headerStyle = this.createDefaultCellStyle();
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(HSSFColor.RED.index);

        Font font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints((short) 12);
        headerStyle.setFont(font);

        return headerStyle;
    }

    /**
     * 创建bodyStyle
     * @return
     */
    public CellStyle createBodyStyle(){
        return createDefaultCellStyle();
    }

    /**
     * 根据配置初始化单元格样式，取值为xml的cellElement的align（水平对齐），新增其他在这里做解析
     */
    private void initHeaderStyles(){
        headerStyles = new ArrayList<>();

        List<Element> elements = excelXml.getCellElements();
        for (Element element : elements) { // 根据xml的cell的属性配置单元格样式，新增其他样式在这里新增
            CellStyle headerStyle = createHeaderStyle();
            headerStyle.setAlignment(Short.valueOf(element.attributeValue("align")));

            headerStyles.add(headerStyle);
        }
    }

    /**
     * 初始化headerValue，取值为xml的cellElement的desc
     */
    private void initHeaderValues(){
        headerValues = new ArrayList<>();
        List<Element> cellElements = excelXml.getCellElements();
        for (Element element : cellElements) {
            headerValues.add(element.attributeValue("desc"));
        }
    }

    /**
     * 根据配置初始化单元格样式，取值为xml的cellElement的align（水平对齐），新增其他在这里做解析
     */
    private void initBodyStyles(){
        bodyStyles = new ArrayList<>();

        List<Element> elements = excelXml.getCellElements();
        for (Element element : elements) { // 根据xml的cell的属性配置单元格样式，新增其他样式在这里新增
            CellStyle bodyStyle = createBodyStyle();
            bodyStyle.setAlignment(Short.valueOf(element.attributeValue("align")));

            bodyStyles.add(bodyStyle);
        }
    }

    /**
     * 根据入参的list初始化bodyValues，xml新增数据解析放在这里
     */
    private void initBodyValues(){
        bodyValues = new ArrayList<>();
        List<Element> cellElements = excelXml.getCellElements();

        for (Object obj : list) {
            List bodyValue = new ArrayList();

            if (obj instanceof Map) { // 生成的数据是Map
                Map<String, Object> map = (Map<String, Object>) obj;
                for (int i = 0; i < cellElements.size(); i++){
                    Element cellElement = cellElements.get(i);
                    bodyValue.add(formatBodyValue(cellElement, map.get(cellElement.attributeValue("name")))); // 根据xml的cell项的name属性的值从map获取值
                }
            } else { // 生成数据是javaBean
                Class<?> objClass = obj.getClass();
                for (int i = 0; i < cellElements.size(); i++){
                    Element cellElement = cellElements.get(i);
                    String key = cellElement.attributeValue("name");
                    if (key.split("\\.").length > 1){ // 解析复杂对象时，递归获取对象，直到最后获取属性的值
                        String[] keys = key.split("\\.");
                        Object value = obj;
                        for (int j = 0; j < keys.length; j++) {
                            if (j != keys.length - 1) {
                                value = getObjectByKey(value, keys[j]);
                            } else {
                                bodyValue.add(formatBodyValue(cellElement, getObjectByKey(value, keys[j])));
                            }
                        }
                    } else {
                        //设置单元格的值
                        bodyValue.add(formatBodyValue(cellElement, getObjectByKey(obj, key)));
                    }
                }
            }

            bodyValues.add(bodyValue);
        }
    }

    /**
     * 获取obj属性key对应的值
     * @param obj
     * @param key
     * @return
     */
    private Object getObjectByKey(Object obj, String key) {
        Object result = null;
        Class<?> objClass = obj.getClass();
        try {
            Field field = objClass.getDeclaredField(key);
            field.setAccessible(true);
            //设置单元格的值
            result = field.get(obj);
        } catch (IllegalAccessException | NoSuchFieldException e) {
            e.printStackTrace();
        }

        return result;
    }

    /**
     * 格式化单元格的数据,xml配置新的数据格式，请在这里格式化
     * @param cellElement
     * @param obj
     */
    private Object formatBodyValue(Element cellElement, Object obj) {
        if (obj == null) {
            return null;
        }

        String type = cellElement.attributeValue("type"); // type(1:string 2:numeric 3:datatime)
        String format = cellElement.attributeValue("format"); // column format(numeric format:0.00|#.##..., datatime format:yyyy-MM-dd...)

        // 设置Cell的值
        switch (type){
            case "1" :
                return String.valueOf(obj);
            case "2" :
                if (obj instanceof Double){ // Double时需要格式化
                    if (format == null || format.length() <= 0){
                        format = "0.00"; //默认保留两位小叔
                    }
                    DecimalFormat df = new DecimalFormat(format);
                    return df.format(obj);
                } else {
                    return String.valueOf(obj);
                }
            case "3" :
                if (format == null && format.length() <= 0){
                    format = "yyyy-MM-dd hh:mm:ss"; //格式化时间
                }
                SimpleDateFormat formatter = new SimpleDateFormat(format);

                if (obj instanceof Date){
                    return formatter.format(obj);
                } else if (obj instanceof Integer){

                } else {
                    try {
                        return formatter.format(formatter.parse(String.valueOf(obj)));
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }


            default :
                return String.valueOf(obj);
        }

    }

    /**
     * 生成Workbook
     */
    public void genWorkbook(){
        Element sheeElement = excelXml.getSheetElement();
        sheet = wb.createSheet(sheeElement.attributeValue("desc"));

        // 设置表格全局样式
        initSheetStyle();

        // 设置表格数据
        setSheetValue(sheet, excelXml, list);
    }

    /**
     * 设置表格数据
     * @param sheet
     * @param excelXml
     * @param list
     */
    private void setSheetValue(Sheet sheet, ExcelXml excelXml, List list) {
        // 设置第一行表头
        Element headerElement = excelXml.getHeaderElement();
        boolean isShow = excelXml.isHasHeader();
        if (isShow) { // 显示第一行
            setHeader(sheet.createRow(0));
        }

        // 设置表格数据
        for (int i = 0 ; i < bodyValues.size(); i++) {
            setBody(sheet.createRow(isShow ? i + 1 : i), bodyValues.get(i));
        }
    }

    /**
     * 设置表头数据和样式
     * @param row
     */
    private void setHeader(Row row) {
        for (int i = 0; i < headerValues.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headerStyles.get(i));
            cell.setCellValue(headerValues.get(i));
        }
    }

    /**
     * 设置行数据和样式
     * @param row
     * @param bodyValue
     */
    private void setBody(Row row, List bodyValue) {
        for (int i = 0; i < bodyValue.size(); i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(bodyStyles.get(i));
            cell.setCellValue(String.valueOf(bodyValue.get(i)));
        }
    }


    /**
     * 设置sheet的样式，暂时有column的宽度、水平对齐方式
     */
    private void initSheetStyle(){
        Element headerElement = excelXml.getHeaderElement();
        List<Element> cells = headerElement.elements("cell");
        String width = null; // 列的宽度
        for (int i = 0; i < cells.size(); i++){
            width = cells.get(i).attributeValue("width");
            if (width != null && width.length() > 0){
                sheet.setColumnWidth(i, Integer.valueOf(width));
            }
        }
    }

    public Workbook getWb() {
        return wb;
    }

    public void setWb(Workbook wb) {
        this.wb = wb;
    }

    public ExcelXml getExcelXml() {
        return excelXml;
    }

    public void setExcelXml(ExcelXml excelXml) {
        this.excelXml = excelXml;
    }

    public Sheet getSheet() {
        return sheet;
    }

    public void setSheet(Sheet sheet) {
        this.sheet = sheet;
    }

    public List getList() {
        return list;
    }

    public void setList(List list) {
        this.list = list;
    }

    public List<String> getHeaderValues() {
        return headerValues;
    }

    public void setHeaderValues(List<String> headerValues) {
        this.headerValues = headerValues;
    }

    public List<CellStyle> getHeaderStyles() {
        return headerStyles;
    }

    public void setHeaderStyles(List<CellStyle> headerStyles) {
        this.headerStyles = headerStyles;
    }

    public List<List<String>> getBodyValues() {
        return bodyValues;
    }

    public void setBodyValues(List<List<String>> bodyValues) {
        this.bodyValues = bodyValues;
    }

    public List<CellStyle> getBodyStyles() {
        return bodyStyles;
    }

    public void setBodyStyles(List<CellStyle> bodyStyles) {
        this.bodyStyles = bodyStyles;
    }
}
